﻿--- NOTE ---

--- MỖI THÁNG CẦN UPDATE ---
--- 1. Ngày đầu tháng
--- 2. Bảng chuyển DAO Thẻ các tháng trước, bảng CIF QW, CIF NGoại lệ các tháng trước
--- 3. Cột tháng trong bảng KPIs Target
--- 4. Sửa công thức trong cách lấy KHHĐ tăng thêm
--- 5. Bảng Dư nợ t háng T-3

--- 1 - Lấy các bảng đầu vào và update thông tin---
--- 1.1 --- Lấy bảng Khách hàng


select * into SME2017DAILY.DBO.VPB_CUSTOMER_20170420
from M74.BICDATA.DBO.CUSTOMER where SEGMENT not in ('KHCN','HH')
--- 1.2 --- Lấy bảng KHHĐ (sửa tên bảng, nội dung)
select * into SME2017DAILY.DBO.KHHD_20170420
from server12.tranglt9.dbo.active_customer_daily_report
where report_date = '2017-04-11' and STATUS = 'ACTIVE'

--- 1.3 --- Lấy bảng Dư nợ, Huy động Bình quân ---
select * into SME2017DAILY.dbo.balance20170420_sme
from GIANGLH3.DAILY.[dbo].[AVGBAL20170420]

--select * into SME2017DAILY.dbo.balance20170420_sme
--from GIANGLH3.DAILY.[dbo].[AVGBAL201703]

alter table SME2017DAILY.dbo.balance20170420_sme
add PRODUCT_DETAIL NVARCHAR (255) 

UPDATE SME2017DAILY.dbo.balance20170420_sme 
SET PRODUCT_DETAIL = '6.3. Unsecured Overdraft'
FROM SME2017DAILY.dbo.balance20170420_sme  AS A , (SELECT * FROM  M74.bicdata.dbo.lntb_disbursement 
WHERE (OVERDRAFT_COLLATERAL = 'N' OR OVERDRAFT_COLLATERAL IS NULL)) AS b 
WHERE A.ACCTNO collate database_default = B.ACCTNO AND A.PRODUCT_NAME = '6. Overdraft' 


--- Cập nhật DAO_CIF
update SME2017DAILY.dbo.balance20170420_sme 
set DAO_CIF = null

UPDATE SME2017DAILY.dbo.balance20170420_sme
	SET DAO_CIF = B.DAO
	FROM SME2017DAILY.dbo.balance20170420_sme A , SME2017DAILY.DBO.VPB_CUSTOMER_20170420 B
	WHERE A.CIF collate database_default = B.RECID

alter table SME2017DAILY.dbo.balance20170420_sme 
add RRT_ID nvarchar (100)

UPDATE SME2017DAILY.dbo.balance20170420_sme --- code update thêm RRT_ID từ bảng T24CRD
	SET RRT_ID = B.RRT_ID
	from SME2017DAILY.dbo.balance20170420_sme A, (SELECT CONTRACT_NO, RRT_ID from M16.VPB_WHR2.DBO.T24CRD where business_date ='2017-04-11' and ISNUMERIC(RRT_ID) = 1
	AND NOT (RRT_ID LIKE '%.%')  AND LEN(RRT_ID) <= 18) B
	where A.ACCTNO collate database_default  = B.CONTRACT_NO 

alter table SME2017DAILY.dbo.balance20170420_sme 
add APP_REJ_DATE date

Update SME2017DAILY.dbo.balance20170420_sme
Set APP_REJ_DATE = B.APP_REJ_DATE
From SME2017DAILY.dbo.balance20170420_sme A, M74.BICDATA.DBO.FINNONE_LIVE B 
where A.RRT_ID COLLATE DATABASE_DEFAULT  = B.APP_ID_C and B.business_date ='2017-04-11' and BI_APPSSTATUS = 'APPROVED'

Update SME2017DAILY.dbo.balance20170420_sme 
Set APP_REJ_DATE = B.APP_RE_CAN_DATE
From DSA.DBO.CLOS_20170310 B 
where RRT_ID COLLATE DATABASE_DEFAULT = B.SZ_APPLICATION_NO

Update SME2017DAILY.dbo.balance20170420_sme
Set APP_REJ_DATE = convert(date,ISSUE_DATE) 
where APP_REJ_DATE is null

alter table SME2017DAILY.dbo.balance20170420_sme
add LOAI_THE nvarchar (40)

select * into #loai_the from M16.[VPB_WHR2].[dbo].[CARD_ISSUE_T24] where limit_ref like '%06%' or limit_ref like '%05%'

update SME2017DAILY.dbo.balance20170420_sme
set LOAI_THE = CASE WHEN SOURCE_ID in (select CARD_ID from #loai_the where limit_ref like '%05%') then 'CTSDB'
				WHEN SOURCE_ID in (select CARD_ID from #loai_the where limit_ref like '%06%') then 'KTSDB' end

--- 1.4 --- Lấy bảng CIF chuyển BAD BANK trong tháng
SELECT distinct CIF, DATE_GBBB INTO SME2017DAILY.DBO.BADBANK_20170420
FROM M16.[VPB_G3B].[DBO].[TBL_G3B_T24CRD] 
			WHERE BUSINESS_DATE = '2017-04-20'			
				AND BRANCH_CODE_GBBB = 'VN9990001' 
				AND DATE_GBBB IS NOT NULL and DATE_GBBB between '2017-04-01' and '2017-04-20'

--- Nếu chuyển vào rồi chuyển ra vẫn bị tính thì chạy code như sau
SELECT distinct CIF, DATE_GBBB INTO SME2017DAILY.DBO.BADBANK_20170420
FROM M16.[VPB_G3B].[DBO].[TBL_G3B_T24CRD] 
			WHERE BUSINESS_DATE between '2017-04-01' and '2017-04-20'			
				AND BRANCH_CODE_GBBB = 'VN9990001' 
				AND DATE_GBBB IS NOT NULL and DATE_GBBB between '2017-04-01' and '2017-04-20'

select * into SME2017DAILY.DBO.BADBANK_20170420 from SME2017.DBO.BADBANK_20170331
--- 1.5 --- Lấy bảng LD chuyển BAD BANK trong tháng
select *, 'YES' NOXAU into SME2017DAILY.DBO.BALANCE_BADBANK_20170420
from M74.sme.dbo.incentive20170411
where CIF in (select CIF from SME2017DAILY.DBO.BADBANK_20170420 where CIF is not null) and NHOMNOMOI in ('3','4','5') 

insert into SME2017DAILY.DBO.BALANCE_BADBANK_20170420 (ACCTNO, BAL_QD, CIF, DAO, NOXAU)
select ACNT_CONTRACT_ID, case when BALANCE >= 0 then 0 else -BALANCE end, CONTRACT_CIF, T24_DAO,'YES'
from M74.BICDATA.DBO.CARD_LIVE where business_date ='2017-04-11'

alter table SME2017DAILY.DBO.BALANCE_BADBANK_20170420
add DAO_CIF nvarchar (50)

update SME2017DAILY.DBO.BALANCE_BADBANK_20170420 
SET DAO_CIF = B.DAO
	FROM SME2017DAILY.DBO.BALANCE_BADBANK_20170420  A , SME2017DAILY.DBO.VPB_CUSTOMER_20170420 B
	WHERE A.CIF collate database_default = B.RECID


--- 1.6 --- Lấy bảng CARD
select *  into SME2017DAILY.DBO.CARD_20170420
from M74.BICDATA.DBO.CARD_LIVE
where  BUSINESS_DATE ='2017-04-11' AND (CARD_NUMBER LIKE '559073%' OR CARD_NUMBER LIKE '519930%' or CARD_NUMBER LIKE '522384%')

update SME2017DAILY.DBO.CARD_20170420 set T24_DAO = B.NEW_DAO
from SME2017DAILY.DBO.CARD_20170420 A, SME2017.DBO.CHUYEN_DAO_THE_201703 B
where A.SOURCE_ID = B.SOURCE_ID

update SME2017DAILY.DBO.CARD_20170420 set T24_DAO = B.NEW_DAO
from SME2017DAILY.DBO.CARD_20170420 A, SME2017.DBO.CARD_201703_NGOAILE  B
where A.SOURCE_ID = B.SOURCE_ID


--- 1.7 --- Update lại bảng KPIs Target
drop table SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE
select * into SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE from SME2017.DBO.KPI_TARGET_201703

update SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE
set apr2017 =  b.kpiS_apr2017
from SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE A, SME2017DAILY.DBO.KPIs_201704 B
where A.DAo = B.DAO and A.CODE = B.CODE and B.Branch_code = A.Branch

--- 1.8 --- Update lại KH chuyển giao trong tháng
drop table #CHUYEN_DAOCIF_201704
select RECID, DAO as OLD_DAO into #CHUYEN_DAOCIF_201704 from SME2017.DBO.VPB_CUSTOMER_20170331

alter table #CHUYEN_DAOCIF_201704
add NEW_DAO nvarchar (50)

update #CHUYEN_DAOCIF_201704
set NEW_DAO = B.DAO 
from #CHUYEN_DAOCIF_201704 A, SME2017DAILY.DBO.VPB_CUSTOMER_20170420 B
where A.RECID COLLATE DATABASE_DEFAULT = B.RECID

drop table #tang_KHHD 
select NEW_DAO, count(RECID) CIF into #tang_KHHD
from #CHUYEN_DAOCIF_201704
where OLD_DAO <> NEW_DAO
AND RECID in (select CIF from SME2016.DBO.KHHD_201612)
and RECID in (Select Recid from SME2017DAILY.DBO.VPB_CUSTOMER_20170420 where  ((channel in ('DSASME')  and datediff(day, CUS_OPEN_DATE, '2017-04-20')>120 ) or Channel not in   ('BDO1','BDO2','BDO3','BDO4','BDO5','MBD1','MBD2','MBD3','MBD4','MBD5','DSASME') or channel is null)
	AND SEGMENT='SMES' )
group by NEW_DAO 

drop table #giam_KHHD 
select OLD_DAO, count(RECID) CIF into #giam_KHHD
from #CHUYEN_DAOCIF_201704
where OLD_DAO <> NEW_DAO
and RECID in (select CIF from SME2016.DBO.KHHD_201612)
and RECID in (Select Recid from SME2017DAILY.DBO.VPB_CUSTOMER_20170420 where  ((channel in ('DSASME')  and datediff(day, CUS_OPEN_DATE, '2017-04-20')>120 ) or Channel not in  ('BDO1','BDO2','BDO3','BDO4','BDO5','MBD1','MBD2','MBD3','MBD4','MBD5','DSASME') or channel is null)
	AND SEGMENT='SMES' )
group by OLD_DAO


update SME2017.DBO.KPIs_201704
set ban_giao_201704 = B.CIF
from SME2017.DBO.KPIs_201704 a, #giam_KHHD b
where a.DAO collate DATABASE_DEFAULT = B.OLD_DAO and a.code = '7'


update SME2017.DBO.KPIs_201704
set nhan_ban_giao_201704 = B.CIF
from SME2017.DBO.KPIs_201704 a, #tang_KHHD b
where a.DAO collate DATABASE_DEFAULT = B.NEW_DAO and a.code = '7'

--- 2. --- CHẠY CODE
--- 2.1 --- Lấy mẫu bảng
select * into SME2017DAILY.DBO.FINAL_MSBO_20170420
from SME2017.DBO.FINAL_MSBO_201702

delete SME2017DAILY.DBO.FINAL_MSBO_20170420

--- 2.2 ---lấy các mã DAO có trong KPI target và nhân sự
insert into SME2017DAILY.DBO.FINAL_MSBO_20170420 (DAO)
select distinct DAO from SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE --- cập nhật hàng tháng
where (position like '%MBO%' or POSITION like'%SBO%') and DAO is not null  

insert into SME2017DAILY.DBO.FINAL_MSBO_20170420 (DAO)
select distinct DAO from SME2017.DBO.HR_SME_201703
where (position like '%MBO%' or POSITION like'%SBO%') and DAO is not null and DAO collate database_default not in 
(select convert ( nvarchar,dao,112) from SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE where dao is not null)

--- 2.3 ---Update KPI target 
Update SME2017DAILY.DBO.FINAL_MSBO_20170420
SET AVGBAL_LOAN_TARGET = APR2017 ---- Dư nợ BQ -----
From SME2017DAILY.DBO.FINAL_MSBO_20170420 AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = '1'

Update SME2017DAILY.DBO.FINAL_MSBO_20170420
SET  AVGBAL_DEPOSIT_TARGET= APR2017 ----- Huy động BQ ----
From SME2017DAILY.DBO.FINAL_MSBO_20170420 AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = '2'

Update SME2017DAILY.DBO.FINAL_MSBO_20170420
SET NEWNPL_LOAN_TARGET = APR2017 --- Nợ xấu ----
From SME2017DAILY.DBO.FINAL_MSBO_20170420 AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = '3'

Update SME2017DAILY.DBO.FINAL_MSBO_20170420
SET  NFI_TARGET= APR2017 ---- Phí ----
From SME2017DAILY.DBO.FINAL_MSBO_20170420 AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = '4'

Update SME2017DAILY.DBO.FINAL_MSBO_20170420
SET NII_TARGET = APR2017 ----- Lãi ---
From SME2017DAILY.DBO.FINAL_MSBO_20170420 AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = '5'

Update SME2017DAILY.DBO.FINAL_MSBO_20170420
SET AVGBAL_UPL_LOAN_TARGET = APR2017 --- Tín chấp BQ----
From SME2017DAILY.DBO.FINAL_MSBO_20170420 AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = '6'


Update SME2017DAILY.DBO.FINAL_MSBO_20170420
SET CUSTOMER_TARGET = APR2017 ---- Khách hàng hoạt động ----
From SME2017DAILY.DBO.FINAL_MSBO_20170420 AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = '7'

Update SME2017DAILY.DBO.FINAL_MSBO_20170420
SET CREDIT_CARD_TARGET = APR2017 --- Thẻ ----
From SME2017DAILY.DBO.FINAL_MSBO_20170420 AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = '8'


--- 2.4 --- Thông tin về nhân viên
Update SME2017DAILY.DBO.FINAL_MSBO_20170420
SET SALE_NAME = B.SALE_NAME,
Branch_code = B.Branch_code,
position = B.position_KPI,
time_work = datediff (day, B.DATE_SALES_start, '2017-04-30')/30, 
REGION = b. REGION,
Staff_id = b.Staff_id,
DEPT = B.POSITION
from SME2017DAILY.DBO.FINAL_MSBO_20170420 as A, SME2017.DBO.HR_SME_201703 as B
where A.DAO collate DATABASE_DEFAULT = B.DAO

--- 2.5 --- Tính số thực tế
--- 2.5.1 --- Dư nợ tín chấp: 
Update SME2017DAILY.DBO.FINAL_MSBO_20170420
set AVGBAL_UPL_LOAN_ACT = B.AMT
from SME2017DAILY.DBO.FINAL_MSBO_20170420 as A, 
(select distinct DAO as DAO, sum(AVGBAL_QD)/1E6 as AMT 
from SME2017DAILY.dbo.balance20170420_sme
where (F is null or F ='B' or (F= 'Y' and BRANCH_code = 'VN0010338' and BI_SEGMENT = 'SMEs'))
	and (SEGMENT not in ('KHCN','HH') and (branch_code not in ('VN0010289','VN0010344','VN0010011','VN0010012','VN0010006','VN0010007','VN0010002','VN0010003','VN0010010','VN0010324','VN0010325',
	'VN0010335','VN0010336','VN0010005','VN0010242','VN0010009','VN0010001','VN0010004','VN0010008','VN0010260','VN0010326','VN0010327','VN0010245','VN0010136') or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = 'LOAN' 
	and DAO is not null
	and CIF collate DATABASE_DEFAULT not in (select cif from SME2017DAILY.DBO.BADBANK_20170420 where CIF is not null) 
	and (CHUONG_TRINH_SP is null or CHANNEL is null or (CHUONG_TRINH_SP not in ('LD-13-SME CLEAN','LD-07-SME-BIL','LD-766-MINI BIL','LD-762-SME-BIL2', 'LD-875-SME-BIL AUTO', 'LD-876-SME-BIL TAI CAP','LD-934-SME BIL EWS')
	and CHUONG_TRINH_SP not like '%BIL%') or (channel in ('DSASME')  and datediff(day, APP_REJ_DATE, '2017-04-20')>90) or Channel not in ('BDO1','BDO2','BDO3','BDO4','BDO5','MBD1','MBD2','MBD3','MBD4','MBD5','DSASME'))
	and (CHUONG_TRINH_SP in ('LD-13-SME CLEAN','LD-07-SME-BIL','LD-766-MINI BIL','LD-762-SME-BIL2', 'LD-875-SME-BIL AUTO', 'LD-876-SME-BIL TAI CAP','LD-934-SME BIL EWS') or 
	(Product_name = '6. Overdraft' and PRODUCT_DETAIL ='6.3. Unsecured Overdraft') or CHUONG_TRINH_SP like '%BIL%') and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_201703 where CIF_DACTHU is not null)
	and (DAO collate DATABASE_DEFAULT = DAO_CIF or CIF collate DATABASE_DEFAULT in (select CIF from SME2017.DBO.CIF_NGOAILE_201703 where CIF is not null))
	group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 

--Cộng số avgbal trong thẻ nếu thẻ là KTSDB --- Nếu tạm thời chưa có bảng dư nợ thẻ thì thôi cuối tháng mới cộng
update SME2017DAILY.DBO.FINAL_MSBO_20170420
set AVGBAL_UPL_LOAN_ACT = case when AVGBAL_UPL_LOAN_ACT is null then 0 else AVGBAL_UPL_LOAN_ACT END + case when 
B.Amt is null then 0 else B.Amt END 
from SME2017DAILY.DBO.FINAL_MSBO_20170420 as A, (select distinct DAO, sum(avgbal_QD)/1E6 as amt from SME2017DAILY.dbo.balance20170420_sme
where (DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM SME2017.DBO.CIF_NGOAILE_201703 where CIF is not null))
AND (LOAI_THE ='KTSDB' or LOAI_THE is null)
AND APP ='CREDIT CARD'
group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 

--- 2.5.2 Dư nợ BQ

Update SME2017DAILY.DBO.FINAL_MSBO_20170420
set AVGBAL_LOAN_ACT = B.AMT
from SME2017DAILY.DBO.FINAL_MSBO_20170420 as A,
(select distinct DAO as DAO, sum(AVGBAL_QD)/1E6 as AMT 
from SME2017DAILY.dbo.balance20170420_sme
where (F is null or F ='B' or (F= 'Y' and BRANCH_CODE = 'VN0010338' and BI_SEGMENT = 'SMEs'))
	AND (SEGMENT not in ('KHCN','HH') and (branch_code not in ('VN0010289','VN0010344','VN0010011','VN0010012','VN0010006','VN0010007','VN0010002','VN0010003','VN0010010','VN0010324','VN0010325',
	'VN0010335','VN0010336','VN0010005','VN0010242','VN0010009','VN0010001','VN0010004','VN0010008','VN0010260','VN0010326','VN0010327','VN0010245','VN0010136') or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = 'LOAN'
	AND ((channel in ('DSASME')  and datediff(day, APP_REJ_DATE, '2017-04-20')>90 ) or Channel not in  ('BDO1','BDO2','BDO3','BDO4','BDO5','MBD1','MBD2','MBD3','MBD4','MBD5','DSASME') or channel is null)
	and DAO is not null
	and CIF collate DATABASE_DEFAULT not in (select cif from SME2017DAILY.DBO.BADBANK_20170420 where CIF is not null)
	and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_201703 where CIF_DACTHU is not null) 
	and (DAO collate DATABASE_DEFAULT = DAO_CIF or CIF collate DATABASE_DEFAULT in (select CIF from SME2017.DBO.CIF_NGOAILE_201703 where CIF is not null))
group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 

--- Cộng số avgbal trong thẻ
update SME2017DAILY.DBO.FINAL_MSBO_20170420
set AVGBAL_LOAN_ACT = case when AVGBAL_LOAN_ACT is null then 0 else AVGBAL_LOAN_ACT END + case when 
B.Amt is null then 0 else B.Amt END
from SME2017DAILY.DBO.FINAL_MSBO_20170420 as A, (select distinct DAO, sum(avgbal_QD)/1E6 as amt from SME2017DAILY.dbo.balance20170420_sme
where (DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM SME2017.DBO.CIF_NGOAILE_201703 where CIF is not null))
AND APP ='CREDIT CARD'
group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 

--- 3. Tính tổng huy động 
-- Lấy từ bảng Balance, Thỏa mãn F là null hoặc = B; BI_segment là SMEs;
--- APP là Deposit; DAO ko null; CHANNEL khác 'SMETLS' và CHANNEL khác'PSASME' hoặc channel là null; CIF ko nằm trong CIF đặc thù; 
--DAO = DAO_CIF hoặc CIF nằm trong CIF ngoại lệ;
Update SME2017DAILY.DBO.FINAL_MSBO_20170420
set AVGBAL_DEPOSIT_ACT =  B.AMT
from SME2017DAILY.DBO.FINAL_MSBO_20170420 as A,
(select distinct DAO as DAO, sum(AVGBAL_QD)/1E6 as AMT 
from SME2017DAILY.dbo.balance20170420_sme
where (F is null or F ='B' or (F= 'Y' and BRANCH_CODE = 'VN0010338' and BI_SEGMENT = 'SMEs'))
 AND (SEGMENT not in ('KHCN','HH') and (branch_code not in ('VN0010289','VN0010344','VN0010011','VN0010012','VN0010006','VN0010007','VN0010002','VN0010003','VN0010010','VN0010324','VN0010325',
	'VN0010335','VN0010336','VN0010005','VN0010242','VN0010009','VN0010001','VN0010004','VN0010008','VN0010260','VN0010326','VN0010327','VN0010245','VN0010136') or (SEGMENT is null and LEFT(sector,1)>1)))
and APP = 'DEPOSIT'
and DAO is not null
and (channel <> 'SMETLS' and channel <> 'PSASME' or channel is null)  ---Channel CTV 
and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_201703  where CIF_DACTHU is not null)
and (DAO collate DATABASE_DEFAULT = DAO_CIF or CIF collate DATABASE_DEFAULT in (select CIF from SME2017.DBO.CIF_NGOAILE_201703 where CIF is not null))
group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 

--- 2.5.4 Tính NPL:
---Bảng Nợ xấu
drop table SME2017DAILY.DBO.NPL_20170420
Select distinct DAO into SME2017DAILY.DBO.NPL_20170420 from  SME2017DAILY.DBO.FINAL_MSBO_20170420 

alter table SME2017DAILY.DBO.NPL_20170420
add NO_XAU_T float,
	DU_NO_THANG_T3 float,
	TI_LE float

Update SME2017DAILY.DBO.NPL_20170420
set NO_XAU_T = B.AMT
FROM  SME2017DAILY.DBO.NPL_20170420 AS A, 
	(SELECT DISTINCT DAO DAO ,isnull(SUM(CASE WHEN NOXAU = 'YES' THEN ISNULL(BAL_QD,0) END),0) AMT 
	FROM SME2017DAILY.DBO.BALANCE_BADBANK_20170420  
	WHERE ACCTNO COLLATE DATABASE_DEFAULT in (select ACCTNO from SME2017DAILY.dbo.balance20170420_sme where (F IS NULL OR F ='B' or (F= 'Y' and BRANCH_CODE = 'VN0010338' and BI_SEGMENT = 'SMEs'))
	AND DAO IS NOT NULL
	AND (SEGMENT not in ('KHCN','HH') and (branch_code not in ('VN0010289','VN0010344','VN0010011','VN0010012','VN0010006','VN0010007','VN0010002','VN0010003','VN0010010','VN0010324','VN0010325',
	'VN0010335','VN0010336','VN0010005','VN0010242','VN0010009','VN0010001','VN0010004','VN0010008','VN0010260','VN0010326','VN0010327','VN0010245','VN0010136') or (SEGMENT is null and LEFT(sector,1)>1)))
	AND APP='LOAN'
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF FROM SME2016.DBO.BADBANK_20161231)
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF_DACTHU FROM SME2017.DBO.CIF_DACTHU_201703 where CIF_DACTHU is not null) 
	AND (CHUONG_TRINH_SP IS NULL or (channel in ('DSASME')  and datediff(day, APP_REJ_DATE, '2017-04-20')>90 ) or Channel not in  ('BDO1','BDO2','BDO3','BDO4','BDO5','MBD1','MBD2','MBD3','MBD4','MBD5','DSASME') or channel is null)
	AND ( DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_201703 where CIF is not null)))
	GROUP BY DAO )  AS B
	WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO 

Update SME2017DAILY.DBO.NPL_20170420 --- xin code update vào
set DU_NO_THANG_T3 = B.AMT
FROM  SME2017DAILY.DBO.NPL_20170420 AS A, 
	(SELECT DISTINCT DAO DAO ,ISNULL(SUM(ISNULL(BAL_QD,0)),0) AMT 
	FROM SME2017.DBO.BALANCE20170131_SME 
	WHERE (F IS NULL OR F ='B' or (F= 'Y' and BRANCH_CIF = 'VN0010338' and BI_SEGMENT = 'SMEs'))
	AND DAO IS NOT NULL
	AND (SEGMENT not in ('KHCN','HH') and (branch_code not in ('VN0010289','VN0010344','VN0010011','VN0010012','VN0010006','VN0010007','VN0010002','VN0010003','VN0010010','VN0010324','VN0010325',
	'VN0010335','VN0010336','VN0010005','VN0010242','VN0010009','VN0010001','VN0010004','VN0010008','VN0010260','VN0010326','VN0010327','VN0010245','VN0010136') or (SEGMENT is null and LEFT(sector,1)>1)))
	AND APP='LOAN'
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF FROM SME2017.DBO.BADBANK_20170131)
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF_DACTHU FROM SME2017.DBO.CIF_DACTHU_201701 where CIF_DACTHU is not null) 
	AND  (CHUONG_TRINH_SP IS NULL or (channel in ('DSASME')  and datediff(day, APP_REJ_DATE, '2017-01-31')>90 ) or Channel not in ('BDO1','BDO2','BDO3','BDO4','BDO5','MBD1','MBD2','MBD3','MBD4','MBD5','DSASME') or channel is null)
	AND ( DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_201701 where CIF is not null))
	GROUP BY DAO )  AS B
	WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO 

	update SME2017DAILY.DBO.NPL_20170420
	set DU_NO_THANG_T3 = case when DU_NO_THANG_T3 is null then 0 else DU_NO_THANG_T3 END + case when 
	B.Amt is null then 0 else B.Amt END
	from SME2017DAILY.DBO.NPL_20170420 as A, (select distinct T24_DAO as DAO, sum( case when balance >= 0 then 0 
	when balance is null then 0 when balance < 0 then balance* -1 end)/1E6 as amt from SME2017.DBO.CARD_20170131 where BUSINESS_DATE ='2017-01-31'
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null and datediff(day, UNLOCK_DATE, '2017-01-31')<90)
	AND ( t24_DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CONTRACT_CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM SME2017.DBO.CIF_NGOAILE_201701 where CIF is not null))
	group by T24_DAO) as B
	where A.DAO collate DATABASE_DEFAULT = B.DAO

update SME2017DAILY.DBO.NPL_20170420 set TI_LE = ISNULL(NO_XAU_T,0)/isnull(DU_NO_THANG_T3,0) where DU_NO_THANG_T3 is not null and DU_NO_THANG_T3 <> 0

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420
	SET NEWNPL_LOAN_ACT = case when B.DU_NO_THANG_T3 is null and B.NO_XAU_T > 0 then 1 else B.TI_LE end
	FROM  SME2017DAILY.DBO.FINAL_MSBO_20170420 AS A, SME2017DAILY.DBO.NPL_20170420  AS B
	WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO

--- 2.5.5 --- Thẻ
Drop table #the_mo_moi
Drop table #the_dong

CREATE TABLE #the_mo_moi
(CUS NVARCHAR (100), 
DAO NVARCHAR (25), 
POINT FLOAT, 
TYPE NVARCHAR (25),
DAO_CIF NVARCHAR (25)
)

CREATE TABLE #the_dong
(CUS NVARCHAR (100), 
DAO NVARCHAR (25), 
POINT FLOAT, 
TYPE NVARCHAR (25),
DAO_CIF NVARCHAR (25)
)

----MỞ THẺ----
--- Thẻ Credit---

SELECT CONVERT (NVARCHAR, CONTRACT_CIF,112) CONTRACT_CIF, T24_DAO , CONTRACT_NUMBER, CASE WHEN COUNT (DISTINCT ( CARD_NUMBER) ) >3 THEN 3
ELSE COUNT (DISTINCT ( CARD_NUMBER) )   END as AMT, 'CREDITCARD' as TYPE into #The_tin_dung
	FROM SME2017DAILY.DBO.CARD_20170420
	WHERE (CARD_NUMBER LIKE '559073%' OR CARD_NUMBER LIKE '519930%') ---- Mã đầu thẻ Credit của SMEs
	AND CARD_STATUS = 'CARD OK'
	AND CONTRACT_STATUS = 'Account OK'
	AND T24_DAO IS NOT NULL
	AND CONVERT(DATE,CARD_DATE_OPEN) >= '20150101' ---- Không mở trước 2015, trước đó đã đc tính rồi.
	AND CONVERT(DATE,UNLOCK_DATE) BETWEEN '20170101' AND '20170420' --- từ đầu 2017 đến nay
	AND PRODUCTION_STATUS ='READY' AND SOURCE_INFO NOT LIKE '%SMETLS%' AND SOURCE_INFO NOT LIKE '%PSASME%'
	AND (BI_CHANNEL NOT IN ('PSASME') OR BI_CHANNEL IS NULL)
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null)
	GROUP BY CONTRACT_CIF, CONTRACT_NUMBER, T24_DAO  

INSERT INTO #the_mo_moi (CUS, DAO, POINT, type)
SELECT CONVERT (NVARCHAR, CONTRACT_CIF,112) , T24_DAO , CASE WHEN sum(isnull(AMT,0)) >6 THEN 6
ELSE sum(isnull(AMT,0))   END, 'CREDITCARD' 
	FROM #The_tin_dung
	GROUP BY CONTRACT_CIF, T24_DAO

--- Thẻ Debit ---
INSERT INTO #the_mo_moi (CUS, DAO, POINT, type)
SELECT CONTRACT_CIF, T24_DAO , CASE WHEN COUNT (DISTINCT (CARD_NUMBER)) > 2 THEN 1
ELSE COUNT (DISTINCT (CARD_NUMBER))*0.5   END AMT , 'DEBITCARD' 
	FROM SME2017DAILY.DBO.CARD_20170420
	WHERE CARD_NUMBER LIKE '522384%'
	AND CARD_STATUS = 'CARD OK'
	AND CONTRACT_STATUS = 'Account OK'
	AND T24_DAO IS NOT NULL
	AND CONVERT(DATE,CARD_DATE_OPEN) >= '20150101' 
	AND CONVERT(DATE,UNLOCK_DATE) BETWEEN '20170101' AND '20170420'---  từ đầu 2017 đến nay
	AND PRODUCTION_STATUS ='READY' AND SOURCE_INFO NOT LIKE '%SMETLS%' AND SOURCE_INFO NOT LIKE '%PSASME%'
	AND (BI_CHANNEL NOT IN ('PSASME') OR BI_CHANNEL IS NULL)
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null)
	GROUP BY CONTRACT_CIF, T24_DAO  

--- Cập nhật DAO_CIF từ bảng CUSTOMER ----
UPDATE #the_mo_moi  
	SET DAO_CIF = A.DAO 
	FROM #the_mo_moi  AS B , SME2017.DBO.VPB_CUSTOMER_20170131 AS A
	WHERE A.RECID COLLATE DATABASE_DEFAULT = B.CUS

--- THẺ ĐÓNG---
--- Thẻ Credit---

SELECT CONVERT (NVARCHAR, CONTRACT_CIF,112) CONTRACT_CIF, T24_DAO , CONTRACT_NUMBER, CASE WHEN COUNT (DISTINCT ( CARD_NUMBER) ) >3 THEN 3
ELSE COUNT (DISTINCT ( CARD_NUMBER) )   END AMT, 'CREDITCARD' TYPE into #The_tin_dung_dong
	FROM SME2017.DBO.CARD_ENDMONTH_201701
	WHERE (CARD_NUMBER LIKE '559073%' OR CARD_NUMBER LIKE '519930%') ---- Mã đầu thẻ Credit của SMEs
	AND T24_DAO IS NOT NULL
	AND((CONVERT(DATE,CARD_DATE_OPEN) >= '2016-10-01' and ACTUAL_DATE_EXPIRE <= '2017-03-31' and ACTUAL_DATE_EXPIRE >= '2017-01-01') or (CONVERT(DATE,CARD_DATE_OPEN) >= '2017-01-01'))
	AND ACTUAL_DATE_EXPIRE is not null and ACTUAL_DATE_EXPIRE <= '2017-04-20'
	AND PRODUCTION_STATUS ='READY' AND SOURCE_INFO NOT LIKE '%SMETLS%' AND SOURCE_INFO NOT LIKE '%PSASME%'
	AND (BI_CHANNEL NOT IN ('PSASME') OR BI_CHANNEL IS NULL)
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null)
	GROUP BY CONTRACT_CIF, CONTRACT_NUMBER, T24_DAO  

INSERT INTO #the_dong (CUS, DAO, POINT, TYPE)
SELECT CONVERT (NVARCHAR, CONTRACT_CIF,112), T24_DAO , CASE WHEN sum(isnull(AMT,0)) >6 THEN 6
ELSE sum(isnull(AMT,0))    END AMT, 'CREDITCARD'
	FROM #The_tin_dung_dong
	GROUP BY CONTRACT_CIF, T24_DAO  

--- Thẻ Debit ---
INSERT INTO #the_dong (CUS, DAO, POINT, TYPE)
SELECT CONTRACT_CIF, T24_DAO , CASE WHEN COUNT (DISTINCT (CARD_NUMBER)) > 2 THEN 1
ELSE COUNT (DISTINCT (CARD_NUMBER))*0.5   END, 'DEBITCARD' 
	FROM SME2017.DBO.CARD_ENDMONTH_201701
	WHERE CARD_NUMBER LIKE '522384%'
	AND YEARMONTH ='201701'
	AND T24_DAO IS NOT NULL
	AND CONVERT(DATE,CARD_DATE_OPEN) >= '20150101' 
	AND((CONVERT(DATE,CARD_DATE_OPEN) >= '2016-10-01' and ACTUAL_DATE_EXPIRE <= '2017-03-31' and ACTUAL_DATE_EXPIRE >= '2017-01-01') or (CONVERT(DATE,CARD_DATE_OPEN) >= '2017-01-01'))
	AND ACTUAL_DATE_EXPIRE is not null and ACTUAL_DATE_EXPIRE <= '2017-04-20'
	AND PRODUCTION_STATUS ='READY' AND SOURCE_INFO NOT LIKE '%SMETLS%' AND SOURCE_INFO NOT LIKE '%PSASME%'
	AND (BI_CHANNEL NOT IN ('PSASME') OR BI_CHANNEL IS NULL)
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null)
	GROUP BY CONTRACT_CIF, T24_DAO  

--- Cập nhật DAO_CIF từ bảng CUSTOMER ----
UPDATE #the_dong  
	SET DAO_CIF = A.DAO 
	FROM #the_dong  AS B , SME2017DAILY.DBO.VPB_CUSTOMER_20170420 AS A
	WHERE A.RECID COLLATE DATABASE_DEFAULT = B.CUS

---- Update thông tin thẻ thực tế vào bảng FINAL_MSBO---
UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
	SET CREDIT_CARD_ACT = isnull(B.AMT,0) 
	FROM SME2017DAILY.DBO.FINAL_MSBO_20170420  AS A, 
	(SELECT DISTINCT CONVERT(NVARCHAR(5),DAO) DAO, SUM(POINT) AMT
	FROM #the_mo_moi WHERE DAO IS NOT NULL
	AND (DAO_CIF = DAO)
	GROUP BY CONVERT(NVARCHAR(5),DAO)) AS B
WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420  
	SET CREDIT_CARD_ACT = isnull(CREDIT_CARD_ACT,0) - isnull(C.AMT,0) 
	FROM SME2017DAILY.DBO.FINAL_MSBO_20170420  AS A, 
	(SELECT DISTINCT CONVERT(NVARCHAR(5),DAO) DAO, SUM(POINT) AMT
	FROM #the_dong WHERE DAO IS NOT NULL
	AND (DAO_CIF = DAO)
	GROUP BY CONVERT(NVARCHAR(5),DAO)) AS C
	WHERE  A.DAO COLLATE DATABASE_DEFAULT = C.DAO

--- 2.5.6 --- Khách hàng hoạt động tăng thêm

DROP TABLE SME2017DAILY.DBO.KHACH_HANG_20170420 
Select distinct DAO into SME2017DAILY.DBO.KHACH_HANG_20170420  from SME2017DAILY.DBO.FINAL_MSBO_20170420 

alter table SME2017DAILY.DBO.KHACH_HANG_20170420 
add SO_LUY_KE float,
	DEC_2016 float,
	BAN_GIAO float,
	NHAN_BAN_GIAO float,
	SO_MOI float

update SME2017DAILY.DBO.KHACH_HANG_20170420 
set Dec_2016 = CUSTOMER_ACT
from SME2017DAILY.DBO.KHACH_HANG_20170420  A, SME2016.DBO.KPI_2017_DAU_KY B
where A.DAO COLLATE DATABASE_DEFAULT = B.DAO

update SME2017DAILY.DBO.KHACH_HANG_20170420 
set SO_LUY_KE = B.AMOUNT
from SME2017DAILY.DBO.KHACH_HANG_20170420  A, (SELECT DISTINCT  DAO, COUNT (DISTINCT RECID) AMOUNT
	FROM SME2017DAILY.DBO.VPB_CUSTOMER_20170420
	WHERE  RECID COLLATE DATABASE_DEFAULT IN
	(SELECT CIF FROM SME2017DAILY.DBO.KHHD_20170420) 
	AND DAO IS NOT NULL
	AND  ((channel in ('DSASME')  and datediff(day, CUS_OPEN_DATE, '2017-04-20')>120 ) or Channel not in ('BDO1','BDO2','BDO3','BDO4','BDO5','MBD1','MBD2','MBD3','MBD4','MBD5','DSASME') or channel is null)
	and (SEGMENT not in ('KHCN','HH') and (COMPANY_BOOK not in ('VN0010289','VN0010344','VN0010011','VN0010012','VN0010006','VN0010007','VN0010002','VN0010003','VN0010010','VN0010324','VN0010325',
	'VN0010335','VN0010336','VN0010005','VN0010242','VN0010009','VN0010001','VN0010004','VN0010008','VN0010260','VN0010326','VN0010327','VN0010245','VN0010136') or (SEGMENT is null and LEFT(sector,1)>1)))
	GROUP BY DAO) AS B
where A.DAO COLLATE DATABASE_DEFAULT = B.DAO

update SME2017DAILY.DBO.KHACH_HANG_20170420 
set BAN_GIAO = isnull(B.BAN_GIAO_201701,0) + isnull(B.BAN_GIAO_201702,0) + isnull(B.BAN_GIAO_201703,0) + isnull(B.BAN_GIAO_201704,0)
from SME2017DAILY.DBO.KHACH_HANG_20170420  A, SME2017.DBO.KPIs_201704 B
where A.DAO COLLATE DATABASE_DEFAULT = B.DAO and B.CODE = '7'

update SME2017DAILY.DBO.KHACH_HANG_20170420 
set NHAN_BAN_GIAO = isnull(B.NHAN_BAN_GIAO_201701,0)+ isnull(B.NHAN_BAN_GIAO_201702,0) + isnull(B.NHAN_BAN_GIAO_201703,0) + isnull(B.NHAN_BAN_GIAO_201704,0)
from SME2017DAILY.DBO.KHACH_HANG_20170420 A, SME2017.DBO.KPIs_201704 B
where A.DAO COLLATE DATABASE_DEFAULT = B.DAO and B.CODE = '7'

update SME2017DAILY.DBO.KHACH_HANG_20170420 
set SO_MOI = isnull(SO_LUY_KE,0) - isnull(DEC_2016,0) + isnull(BAN_GIAO,0) - isnull (NHAN_BAN_GIAO,0) 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420  
	SET CUSTOMER_ACT = B.SO_MOI
	FROM SME2017DAILY.DBO.FINAL_MSBO_20170420    AS A, 
	SME2017DAILY.DBO.KHACH_HANG_20170420  B
	WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO

--- 2.6 --- Cập nhật lại các dòng NULL
UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET AVGBAL_UPL_LOAN_TARGET   = '0' WHERE AVGBAL_UPL_LOAN_TARGET   IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET AVGBAL_LOAN_TARGET  = '0' WHERE AVGBAL_LOAN_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET AVGBAL_DEPOSIT_TARGET  = '0' WHERE AVGBAL_DEPOSIT_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET NFI_TARGET  = '0' WHERE NFI_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET NII_TARGET  = '0' WHERE NII_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET NEWNPL_LOAN_TARGET  = '0' WHERE NEWNPL_LOAN_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET CREDIT_CARD_TARGET  = '0' WHERE CREDIT_CARD_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET CUSTOMER_TARGET   = '0' WHERE CUSTOMER_TARGET   IS NULL

--- Cập nhật các KPI actual đang bị NULL thành 0 ---
UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET AVGBAL_UPL_LOAN_ACT   = '0' WHERE AVGBAL_UPL_LOAN_ACT   IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET AVGBAL_LOAN_ACT  = '0' WHERE AVGBAL_LOAN_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET AVGBAL_DEPOSIT_ACT  = '0' WHERE AVGBAL_DEPOSIT_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET NFI_ACT  = '0' WHERE NFI_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET NII_ACT  = '0' WHERE NII_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET NEWNPL_LOAN_ACT  = '0' WHERE NEWNPL_LOAN_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET CREDIT_CARD_ACT  = '0' WHERE CREDIT_CARD_ACT  IS NULL OR CREDIT_CARD_ACT  < 0

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420 
SET CUSTOMER_ACT   = '0' WHERE CUSTOMER_ACT   IS NULL OR CUSTOMER_ACT < 0



--- 2.7 --- Tính điểm hoàn thành KPIs: Lấy thực tế chia kế hoạch, Max là 1.5, riêng nợ xấu = 1-(Thực tế - Kế hoạch)/Kế hoạch 
UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420   
SET AVGBAL_UPL_LOAN_REAL = ( CASE WHEN AVGBAL_UPL_LOAN_TARGET = 0 AND AVGBAL_UPL_LOAN_ACT = 0 THEN 1
	WHEN AVGBAL_UPL_LOAN_TARGET = 0 AND AVGBAL_UPL_LOAN_ACT > 0 THEN 1.5
	WHEN AVGBAL_UPL_LOAN_ACT / AVGBAL_UPL_LOAN_TARGET > 1.5 THEN 1.5 ELSE AVGBAL_UPL_LOAN_ACT / AVGBAL_UPL_LOAN_TARGET END ) ,

	AVGBAL_LOAN_REAL = ( CASE WHEN AVGBAL_LOAN_TARGET = 0 AND AVGBAL_LOAN_ACT = 0 THEN 1
	WHEN AVGBAL_LOAN_TARGET = 0 AND AVGBAL_LOAN_ACT > 0 THEN 1.5
	WHEN AVGBAL_LOAN_ACT / AVGBAL_LOAN_TARGET > 1.5 THEN 1.5 ELSE AVGBAL_LOAN_ACT / AVGBAL_LOAN_TARGET END ) ,

	AVGBAL_DEPOSIT_REAL = ( CASE WHEN AVGBAL_DEPOSIT_TARGET = 0 AND AVGBAL_DEPOSIT_ACT = 0 THEN 1
	WHEN AVGBAL_DEPOSIT_TARGET = 0 AND AVGBAL_DEPOSIT_ACT > 0 THEN 1.5
	WHEN AVGBAL_DEPOSIT_ACT / AVGBAL_DEPOSIT_TARGET > 1.5 THEN 1.5 ELSE AVGBAL_DEPOSIT_ACT / AVGBAL_DEPOSIT_TARGET END ) ,

	NFI_REAL = ( CASE WHEN NFI_TARGET = 0 AND NFI_ACT = 0 THEN 1
	WHEN NFI_TARGET = 0 AND NFI_ACT > 0 THEN 1.5
	WHEN NFI_TARGET = 0 AND NFI_ACT < 0 THEN 0
	WHEN NFI_ACT / NFI_TARGET < 0 THEN 0 
	WHEN NFI_ACT / NFI_TARGET > 1.5 THEN 1.5 ELSE NFI_ACT / NFI_TARGET END ) ,

	NII_REAL = ( CASE WHEN NII_TARGET = 0 AND NII_ACT = 0 THEN 1
	WHEN NII_TARGET = 0 AND NII_ACT > 0 THEN 1.5
	WHEN NII_TARGET = 0 AND NII_ACT < 0 THEN 0
	WHEN NII_ACT / NII_TARGET < 0 THEN 0
	WHEN NII_ACT / NII_TARGET > 1.5 THEN 1.5 ELSE NII_ACT / NII_TARGET END ) ,

	NEWNPL_LOAN_REAL =(CASE WHEN NEWNPL_LOAN_TARGET =0 AND NEWNPL_LOAN_ACT =0 THEN 1 
	WHEN NEWNPL_LOAN_TARGET =0 AND NEWNPL_LOAN_ACT > 0 THEN 0
	WHEN NEWNPL_LOAN_TARGET >0 AND NEWNPL_LOAN_ACT = 0 THEN 1.5
	WHEN 1 - (NEWNPL_LOAN_ACT - NEWNPL_LOAN_TARGET)/NEWNPL_LOAN_TARGET < 0 THEN 0 
	WHEN 1 - (NEWNPL_LOAN_ACT - NEWNPL_LOAN_TARGET)/NEWNPL_LOAN_TARGET >1.5 THEN 1.5 
	ELSE 1 - (NEWNPL_LOAN_ACT - NEWNPL_LOAN_TARGET)/NEWNPL_LOAN_TARGET END ),

	CREDIT_CARD_REAL = ( CASE WHEN CREDIT_CARD_TARGET = 0 AND CREDIT_CARD_ACT = 0 THEN 1
	WHEN CREDIT_CARD_TARGET = 0 AND CREDIT_CARD_ACT > 0 THEN 1.5
	WHEN CREDIT_CARD_ACT / CREDIT_CARD_TARGET > 1.5 THEN 1.5 ELSE CREDIT_CARD_ACT / CREDIT_CARD_TARGET END),

	CUSTOMER_REAL = ( CASE WHEN CUSTOMER_TARGET = 0 AND CUSTOMER_ACT = 0 THEN 1
	WHEN CUSTOMER_TARGET = 0 AND CUSTOMER_ACT > 0 THEN 1.5
	WHEN CUSTOMER_ACT / CUSTOMER_TARGET > 1.5 THEN 1.5 ELSE CUSTOMER_ACT / CUSTOMER_TARGET END  )  

--- 2.8 --- Tính điểm hoàn thành KPI tổng: lấy từng điểm thành phần nhân trọng số---
UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420     
		SET KPI_FINAL = (AVGBAL_UPL_LOAN_REAL *0.2 +AVGBAL_LOAN_REAL *0.15 +AVGBAL_DEPOSIT_REAL *0.05 
			 +NFI_REAL *0.15 +NII_REAL*0.2 +NEWNPL_LOAN_REAL*0.05 +CREDIT_CARD_REAL*0.05 +CUSTOMER_REAL *0.15 )  
		WHERE DEPT = 'MBO'

UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420   
		SET KPI_FINAL = (AVGBAL_UPL_LOAN_REAL *0.15 +AVGBAL_LOAN_REAL *0.2 +AVGBAL_DEPOSIT_REAL *0.05 
			 +NFI_REAL *0.15 +NII_REAL*0.25 +NEWNPL_LOAN_REAL*0.05 +CUSTOMER_REAL *0.15)  
		WHERE DEPT = 'SBO'

--- làm tròn điểm tính Incentive ----
UPDATE SME2017DAILY.DBO.FINAL_MSBO_20170420   
		SET KPI_ROUND = ROUND (KPI_FINAL*100,0) 

--- 3 --- In ra bảng KQ ---
select '2017-04-20' BUSINESS_DATE
	   BRANCH_CODE
	  ,SALE_NAME  
      ,DAO 
      ,POSITION  
	  ,TIME_WORK 
      ,REGION 
      ,STAFF_ID 
      , DEPT
	  ,AVGBAL_LOAN_TARGET 
	  ,AVGBAL_DEPOSIT_TARGET 
	  ,NEWNPL_LOAN_TARGET 
      ,AVGBAL_UPL_LOAN_TARGET  
	  ,CUSTOMER_TARGET 
      ,CREDIT_CARD_TARGET 
      ,AVGBAL_LOAN_ACT  
	  ,AVGBAL_DEPOSIT_ACT  
	  ,NEWNPL_LOAN_ACT 
      ,AVGBAL_UPL_LOAN_ACT 
	  ,CUSTOMER_ACT
      ,CREDIT_CARD_ACT 
      ,AVGBAL_LOAN_REAL 
	  ,AVGBAL_DEPOSIT_REAL 
	  ,NEWNPL_LOAN_REAL 
      ,AVGBAL_UPL_LOAN_REAL  
	  ,CUSTOMER_REAL 
      ,CREDIT_CARD_REAL
      ,KPI_FINAL
      ,KPI_ROUND
	  into SME2017DAILY.DBO.FINAL_MSBO_20170420_KQ
	  FROM SME2017DAILY.DBO.FINAL_MSBO_20170420
	  where AVGBAL_UPL_LOAN_TARGET > 0
	  and AVGBAL_LOAN_TARGET > 0
	  and AVGBAL_DEPOSIT_TARGET > 0
	  and sale_name is not null
	  and position is not null
	  and dao collate database_default  not in (N'Không có mã DAO',N'Không có mã DAO')
	  and dao collate database_default in (select dao from SME2017.DBO.HR_SME_201703 where contract_status =N'Chính thức')
	  and dao collate database_default in (select dao from SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE)
	  and BRANCH_CODE not in ('HCM', 'HBH', 'SGN', 'GDH', 'PMH', 'TPU') 
	  order by REGION, branch_code, DAO

--- 4 --- Lấy dữ liệu thô
--- 4.1 DƯ NỢ TÍN CHẤP 
SELECT '20170420' BUSINESS_DATE, BRANCH_CODE, APP, DAO ,DAO_CIF ,ACCTNO, CIF, AVGBAL_QD,PRODUCT_GROUP, CHUONG_TRINH_SP ,convert(date, ISSUE_DATE)  ISSUE_DATE, CHANNEL,
	(CASE WHEN DAO <> DAO_CIF  AND CIF COLLATE DATABASE_DEFAULT NOT IN
	(SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_201703 where CIF is not null) THEN 'KHONG GHI NHAN DO DAO CIF KHAC DAO SP' ELSE '' END ) NOTE
	into SME2017DAILY.dbo.RAW_DU_NO_TIN_CHAP_BQ_20170420
From SME2017DAILY.dbo.balance20170420_sme
	WHERE  (F is null or F ='B' or (F= 'Y' and BRANCH_CODE = 'VN0010338' and BI_SEGMENT = 'SMEs'))
	and (SEGMENT not in ('KHCN','HH') and (BRANCH_CODE not in ('VN0010289','VN0010344','VN0010011','VN0010012','VN0010006','VN0010007','VN0010002','VN0010003','VN0010010','VN0010324','VN0010325',
	'VN0010335','VN0010336','VN0010005','VN0010242','VN0010009','VN0010001','VN0010004','VN0010008','VN0010260','VN0010326','VN0010327','VN0010245','VN0010136') or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = 'LOAN' 
	and DAO is not null
	and CIF collate DATABASE_DEFAULT not in (select cif from SME2017DAILY.DBO.BADBANK_20170420 where CIF is not null) 
	and (CHUONG_TRINH_SP is null or CHANNEL is null or (CHUONG_TRINH_SP not in ('LD-13-SME CLEAN','LD-07-SME-BIL','LD-766-MINI BIL','LD-762-SME-BIL2', 'LD-875-SME-BIL AUTO', 'LD-876-SME-BIL TAI CAP','LD-934-SME BIL EWS')
	and CHUONG_TRINH_SP not like '%BIL%') or (channel in ('DSASME')  and datediff(day, APP_REJ_DATE, '2017-04-20')>90) or Channel not in ('BDO1','BDO2','BDO3','BDO4','BDO5','MBD1','MBD2','MBD3','MBD4','MBD5','DSASME'))
	and (CHUONG_TRINH_SP in ('LD-13-SME CLEAN','LD-07-SME-BIL','LD-766-MINI BIL','LD-762-SME-BIL2', 'LD-875-SME-BIL AUTO', 'LD-876-SME-BIL TAI CAP','LD-934-SME BIL EWS') or 
	(Product_name = '6. Overdraft' and PRODUCT_DETAIL ='6.3. Unsecured Overdraft') or CHUONG_TRINH_SP like '%BIL%') and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_201703 where CIF_DACTHU is not null)
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_201703)
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.KPI_TARGET_201702)
	order by branch_code,DAO

--- 4.2 DƯ NỢ BQ
SELECT  '20170420' BUSINESS_DATE, BRANCH_CODE, APP, DAO ,DAO_CIF ,ACCTNO, CIF, AVGBAL_QD,PRODUCT_GROUP, CHUONG_TRINH_SP ,convert(date, ISSUE_DATE)  ISSUE_DATE, CHANNEL,
	(CASE WHEN DAO <> DAO_CIF  AND CIF COLLATE DATABASE_DEFAULT NOT IN
	(SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_201703 where CIF is not null) THEN 'KHONG GHI NHAN DO DAO CIF KHAC DAO SP' ELSE '' END ) NOTE
	into SME2017DAILY.dbo.RAW_DU_NO_BQ_20170420
	FROM SME2017DAILY.dbo.balance20170420_sme
	WHERE (F is null or F ='B' or (F= 'Y' and BRANCH_CODE = 'VN0010338' and BI_SEGMENT = 'SMEs'))
	and (SEGMENT not in ('KHCN','HH') and (BRANCH_CODE not in ('VN0010289','VN0010344','VN0010011','VN0010012','VN0010006','VN0010007','VN0010002','VN0010003','VN0010010','VN0010324','VN0010325',
	'VN0010335','VN0010336','VN0010005','VN0010242','VN0010009','VN0010001','VN0010004','VN0010008','VN0010260','VN0010326','VN0010327','VN0010245','VN0010136') or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = 'LOAN'
	AND ((channel in ('DSASME')  and datediff(day, APP_REJ_DATE, '2017-04-20')>90 ) or Channel not in  ('BDO1','BDO2','BDO3','BDO4','BDO5','MBD1','MBD2','MBD3','MBD4','MBD5','DSASME') or channel is null)
	and DAO is not null
	and CIF collate DATABASE_DEFAULT not in (select cif from SME2017DAILY.DBO.BADBANK_20170420 where CIF is not null)
	and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_201703 where CIF_DACTHU is not null) 
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_201703)
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.KPI_TARGET_201702)
	order by branch_code,DAO

--- 4.3 Huy động BQ 
SELECT '20170420' BUSINESS_DATE, BRANCH_CODE, APP, DAO ,DAO_CIF ,ACCTNO, CIF, AVGBAL_QD,PRODUCT_GROUP, convert(date, ISSUE_DATE)  ISSUE_DATE ,
(CASE WHEN DAO <> DAO_CIF  AND CIF COLLATE DATABASE_DEFAULT NOT IN
(SELECT CIF FROM  .SME2017.DBO.CIF_NGOAILE_201703 where CIF is not null ) THEN 'KHONG GHI NHAN DO DAO CIF KHAC DAO SP' ELSE '' END ) NOTE 
	into SME2017DAILY.dbo.RAW_HUY_DONG_BQ_20170420
	FROM SME2017DAILY.dbo.balance20170420_sme
	WHERE (F is null or F ='B' or (F= 'Y' and BRANCH_CODE = 'VN0010338' and BI_SEGMENT = 'SMEs'))
	and (SEGMENT not in ('KHCN','HH') and (BRANCH_CODE not in ('VN0010289','VN0010344','VN0010011','VN0010012','VN0010006','VN0010007','VN0010002','VN0010003','VN0010010','VN0010324','VN0010325',
	'VN0010335','VN0010336','VN0010005','VN0010242','VN0010009','VN0010001','VN0010004','VN0010008','VN0010260','VN0010326','VN0010327','VN0010245','VN0010136') or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = 'DEPOSIT'
	and DAO is not null
	and (channel <> 'SMETLS' and channel <> 'PSASME' or channel is null)  ---Channel CTV 
	and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_201703  where CIF_DACTHU is not null)
	and (DAO collate DATABASE_DEFAULT = DAO_CIF or CIF collate DATABASE_DEFAULT in (select CIF from SME2017.DBO.CIF_NGOAILE_201703 where CIF is not null))
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_201703)
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.KPI_TARGET_201702)
	order by branch_code,DAO

--- 4.4 Số lượng KHHĐ
--- 4.4.1 -- KHHD tăng thêm
SELECT '20170420' BUSINESS_DATE, DAO, SO_LUY_KE, DEC_2016, BAN_GIAO, NHAN_BAN_GIAO, SO_MOI as SO_KHHD_TANG_THEM
	INTO SME2017DAILY.dbo.RAW_KHHD_TANG_THEM_20170420
	FROM SME2017DAILY.DBO.KHACH_HANG_20170420 
	WHERE  DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_201703)
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.KPI_TARGET_201702)
	order by DAO

--- Chi tiết KHHĐ
--- Ngày chốt dữ liệu
SELECT '20170420' BUSINESS_DATE ,RECID,CUS_NAME, CUS_OPEN_DATE, DAO, COMPANY_BOOK
	INTO SME2017DAILY.dbo.RAW_KHHD_20170420
	FROM .SME2017DAILY.DBO.VPB_CUSTOMER_20170420
	WHERE  RECID COLLATE DATABASE_DEFAULT IN
	(SELECT CIF FROM SME2017DAILY.DBO.KHHD_20170420) 
	AND DAO IS NOT NULL
	AND  ((channel in ('DSASME')  and datediff(day, CUS_OPEN_DATE, '2017-04-20')>120 ) or Channel not in  ('BDO1','BDO2','BDO3','BDO4','BDO5','MBD1','MBD2','MBD3','MBD4','MBD5','DSASME') or channel is null)
	AND SEGMENT='SMES' 
	AND DAO collate DATABASE_DEFAULT in (select DAO from HR_SME_201703)
	AND DAO collate DATABASE_DEFAULT in (select DAO from KPI_TARGET_20173)
	--and Branch not in ('Q10') 
	order by branch,DAO

--- Tháng 12/2016
SELECT '201612' BUSINESS_DATE,RECID,CUS_NAME, CUS_OPEN_DATE, DAO, COMPANY_BOOK
	INTO SME2017DAILY.dbo.RAW_KHHD_201612
	FROM .SME2016.DBO.VPB_CUSTOMER_20161231
	WHERE  RECID COLLATE DATABASE_DEFAULT IN
	(SELECT CIF from KHHD_201612 ) 
	AND DAO IS NOT NULL
	AND (CHANNEL COLLATE DATABASE_DEFAULT NOT IN ('PSASME','DSASME') OR CHANNEL IS NULL or DATEDIFF (DAY,CUS_OPEN_DATE, '2016-12-31')>120)
	AND SEGMENT='SMES'
	order by DAO


--- 4.5 Nợ xấu
SELECT '20170420' BUSINESS_DATE,BRANCH_CODE, APP, DAO ,DAO_CIF ,ACCTNO, CIF, BAL_QD,PRODUCT_GROUP, CHUONG_TRINH_SP ,convert(date, ISSUE_DATE)  ISSUE_DATE
	into SME2017DAILY.dbo.RAW_NO_XAU_20170420
	FROM SME2017DAILY.DBO.BALANCE_BADBANK_20170420  
	WHERE ACCTNO COLLATE DATABASE_DEFAULT in (select ACCTNO from SME2017DAILY.dbo.balance20170420_sme where (F IS NULL OR F ='B' or (F= 'Y' and BRANCH_CODE = 'VN0010338' and BI_SEGMENT = 'SMEs'))
	AND DAO IS NOT NULL
	AND (SEGMENT not in ('KHCN','HH') and (branch_code not in ('VN0010289','VN0010344','VN0010011','VN0010012','VN0010006','VN0010007','VN0010002','VN0010003','VN0010010','VN0010324','VN0010325',
	'VN0010335','VN0010336','VN0010005','VN0010242','VN0010009','VN0010001','VN0010004','VN0010008','VN0010260','VN0010326','VN0010327','VN0010245','VN0010136') or (SEGMENT is null and LEFT(sector,1)>1)))
	AND APP='LOAN'
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF FROM SME2016.DBO.BADBANK_20161231)
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF_DACTHU FROM SME2017.DBO.CIF_DACTHU_201703 where CIF_DACTHU is not null) 
	AND (CHUONG_TRINH_SP IS NULL or (channel in ('DSASME')  and datediff(day, APP_REJ_DATE, '2017-04-20')>90 ) or Channel not in  ('BDO1','BDO2','BDO3','BDO4','BDO5','MBD1','MBD2','MBD3','MBD4','MBD5','DSASME') or channel is null)
	AND ( DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_201703 where CIF is not null)))
	order by branch_code,DAO

--- 4.6 Thẻ 
	drop table #a
	select ACNT_CONTRACT_ID, ACNT_CONTRACT_OID, CONTRACT_NUMBER, CARD_NUMBER, CARD_STATUS, CARD_DATE_OPEN, PRODUCTION_STATUS,
			CARD_TYPE, UNLOCK_DATE, T24_DAO, CONTRACT_CIF, CONTRACT_STATUS, ACTUAL_DATE_EXPIRE into #a from  SME2017DAILY.DBO.CARD_20170420
			where T24_DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_201703) and (unlock_date >= '2017-01-01' 
			 or 	(((CONVERT(DATE,CARD_DATE_OPEN) >= '2016-10-01' and ACTUAL_DATE_EXPIRE <= '2017-03-31' and ACTUAL_DATE_EXPIRE >= '2017-01-01') or (CONVERT(DATE,CARD_DATE_OPEN) >= '2017-01-01'))
	AND ACTUAL_DATE_EXPIRE is not null and ACTUAL_DATE_EXPIRE <= '2017-04-20'))

	alter table #a
	add DAO_CIF nvarchar (50)

	update #a
	set DAO_CIF= B.DAO 
	from #a A, SME2017DAILY.DBO.VPB_CUSTOMER_20170420 B
	where A.CONTRACT_CIF = B.RECID 

	alter table #a
	add BRANCH nvarchar(50)

	select BRANCH, CONTRACT_NUMBER, CARD_NUMBER, CONTRACT_CIF as CIF, CARD_STATUS,CARD_DATE_OPEN, CARD_TYPE, UNLOCK_DATE, 
	T24_DAO, DAO_CIF, CONTRACT_STATUS, ACTUAL_DATE_EXPIRE, CASE WHEN ACTUAL_DATE_EXPIRE is not null then N'Thẻ đóng' END NOTE 
	into SME2017DAILY.dbo.RAW_THE_20170420
	from #a
	select * from SME2017.DBO.balance201702_sme

	select top 1 * from M74.BICDATA.DBO.BALANCE_LIVE 

	select * from [dbo].[VPB_CUSTOMER_20170420]     [dbo].[KHACH_HANG_20170420] [dbo].[FINAL_MSBO_20170420_KQ]
 select * from [dbo].[RAW_KHHD_TANG_THEM_20170420]

 insert server12.kpi_sme.dbo.[KHHD_20170420]
 select * from [dbo].[KHHD_20170420]

 select * from [dbo].[FINAL_MSBO_20170420_KQ]
 
     [dbo].[RAW_DU_NO_BQ_20170420]

	 select * from SME2017DAILY.DBO.FINAL_MSBO_20170420_KQ